/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package lu.med.praxiswahl.wahlumed.dao.beans;

import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import lu.med.praxiswahl.wahlumed.dao.beans.daointerface.BillDAOInt;
import lu.med.praxiswahl.wahlumed.model.beans.CustomerTBL;
import lu.med.praxiswahl.wahlumed.model.beans.HonorarTBL;
/**
 * 
 * @author Patrick kendzo Email: pkendzo@msn.com
 */

@Stateless
public class BillDAO implements BillDAOInt
{

    @PersistenceContext(unitName = "gynaekomedic-ejbPU")
    private EntityManager em = null;

    public BillDAO()
    {

    }

    @Override
    public void delete(Long billnumber)
    {
	HonorarTBL bill = em.find(HonorarTBL.class, billnumber);

	CustomerTBL cust = bill.getCustomer();

	cust.getOpHonorar().remove(bill);
	
	em.remove(bill);
	em.flush();

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HonorarTBL> findAvailable_ERLEDIGTE_BillsByCustomer(
	    String matricule)
    {
	try
	{
	    return (List<HonorarTBL>) em
		    .createQuery(
			    "select bi from HonorarTBL bi where customer ='"
				    + matricule
				    + "' and bezahlt = true and erstellt = true and HONORAR_KLASSE= '3' order by erstell_datum desc")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException("An -ERLEDIGTE- Rechnung don't exist", e);
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HonorarTBL> findAvailable_ERSTELLTE_BillsByCustomer(
	    String matricule)
    {
	try
	{
	    return (List<HonorarTBL>) em
		    .createQuery(
			    "select bi from HonorarTBL bi where erstellt = true and bezahlt = false and customer ='"
				    + matricule
				    + "' and HONORAR_KLASSE= '3' order by erstell_datum desc")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException("An -ERSTELLTE- Rechnung don't exist", e);
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HonorarTBL> findAvailable_NICHTERSTELLTE_Bills()
    {
	try
	{
	    return (List<HonorarTBL>) em
		    .createQuery(
			    "select bi from HonorarTBL bi where erstellt = false and bezahlt = false and HONORAR_KLASSE= '3' order by erstell_datum desc")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException(
		    "A -NICHTERSTELLTE- Rechnung don't exist", e);
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HonorarTBL> findAvailable_ACTUAL_BillsByCustomer(
	    String matricule)
    {
	try
	{
	    return (List<HonorarTBL>) em
		    .createQuery(
			    "select bi from HonorarTBL bi where bezahlt = false and erstellt = true and customer ='"
				    + matricule
				    + "' and HONORAR_KLASSE= '3' order by erstell_datum desc")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException("An -ACTUAL- Rechnung don't exist", e);
	}
    }

    @Override
    public HonorarTBL findBillsByID(Long billNumber)
    {
	return (HonorarTBL) em.find(HonorarTBL.class, billNumber);
    }

    @Override
    public void persist(HonorarTBL todo)
    {
	em.persist(todo);
	em.flush();

    }

    @Override
    public void update(HonorarTBL todo)
    {
	em.merge(todo);
	em.flush();

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HonorarTBL> findBillsNewerThanOf(String matricule, Calendar date)
    {
	try
	{
	    return (List<HonorarTBL>) em.createQuery(
		    "select b from HonorarTBL b where erstell_datum > '" + date
			    + "'" + " and customer ='" + matricule
			    + "' and HONORAR_KLASSE= '3'").getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException("Rechnungen:: Newer than " + date
		    + "don't exist", e);
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HonorarTBL> findBillsOlderThanOf(String matricule, Calendar date)
    {
	try
	{
	    return (List<HonorarTBL>) em.createQuery(
		    "select b from HonorarTBL b where erstell_datum <= '"
			    + date + "'" + " and customer ='" + matricule
			    + "' and HONORAR_KLASSE= '3'").getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException("Rechnungen:: older than " + date
		    + "don't exist", e);
	}
    }

    @Override
    public Long findDatabaseSubPrimaryKeyIndex()
    {
	try
	{
	    Calendar c = new GregorianCalendar();
	    int actualYear = c.get(Calendar.YEAR);

	    /*
	     * Long result = (Long) em.createQuery(
	     * "select max(rechnung_nummer) from HonorarTBL bi ")
	     * .getSingleResult();
	     */

	    Long result = (Long) em
		    .createQuery(
			    "select max(rechnung_nummer) from HonorarTBL bi where bi.rechnung_nummer like '"
				    + actualYear + "%'").getSingleResult();

	    return result.longValue();
	}
	catch (Exception e)
	{
	    return -1l;
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HonorarTBL> findAvailable_ImPC_Bills()
    {
	try
	{
	    return (List<HonorarTBL>) em
		    .createQuery(
			    "select bi from HonorarTBL bi where imDossier = true and HONORAR_KLASSE= '3' order by erstell_datum desc")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException("A -Im PC- Rechnung don't exist", e);
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public int queryDate(String dateString, String matricule, Long billId)
    {

	try
	{

	    List<HonorarTBL> result = (List<HonorarTBL>) em
		    .createNativeQuery(
			    "SELECT distinct(d.date_dent_heure) FROM praxis.praxis_rechnungs_details d left join praxis.praxis_honorar h "
				    + "on d.RECHNUNGS_ID = h.rechnung_nummer"
				    + " where d.date_dent_heure = '"
				    + dateString
				    + "' "
				    + "and d.RECHNUNGS_ID != '"
				    + billId
				    + "' "
				    + "and h.patientin_matricule_nummer = '"
				    + matricule + "'").getResultList();

	    if (result.size() != 0)
		return result.size();
	    else
		return 0;

	}
	catch (Exception e)
	{
	    throw new RuntimeException(
		    "A problem has occur while query Date Items", e);
	}

    }

}
